﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using BTN04.DTO;
using System.Data.SqlClient;

namespace BTN04.DAO
{
    public class HoaDonDAO
    {
        //ham them hoa don
        public int ThemHoaDon(HoaDonDTO hdDTO)
        {
            string sql_string = "Insert into HoaDon(MaNguoiDung,ThoiGianMua,MaTinhTrangHoaDon) values(" + hdDTO.MaNguoiDung.ToString();
            sql_string += ",'" + hdDTO.ThoiGianMua.ToString() + "'," + hdDTO.MaTinhTrangHoaDon.ToString() + ")";
            SqlConnection connect = new SqlConnection(DataProvider.ConnectionString);
            connect.Open();
            SqlCommand comnd = new SqlCommand(sql_string, connect);
            int kiem = comnd.ExecuteNonQuery();
            sql_string = "Select @@IDENTITY";
            comnd = new SqlCommand(sql_string, connect);
            int ma = Convert.ToInt32(comnd.ExecuteScalar());
            connect.Close();
            return ma;
        }

        //ham cap nhat ma tinh trang hoa don
        public int CapNhatTinhTrangHoaDon(int mahd, int matt)
        {
            string sql = "Update HOADON set MaTinhTrangHoaDon=" + matt.ToString() + " Where MaHoaDon=" + mahd.ToString();
            SqlConnection connect = new SqlConnection(DataProvider.ConnectionString);
            connect.Open();
            SqlCommand comnd = new SqlCommand(sql, connect);
            int kiem = comnd.ExecuteNonQuery();
            connect.Close();
            return kiem;
        }
        //ham thong ke doanh so theo thang
        public DataTable ThongKeDoanhSoTheoThang(int thang,int nam)
        {
            SqlConnection cnn = new SqlConnection(DataProvider.ConnectionString);
            string str;
            if (thang != 0)
            {
                str = "SELECT cthd.MaSanPham,Sum(cthd.SoLuongMua) as SoLuongMua,cthd.GiaBan";
                str += " FROM ChiTietHoaDon cthd, HoaDon hd ";
                str += " WHERE cthd.MaHoaDon = hd.MaHoaDon AND hd.MaTinhTrangHoaDon=1 And Month(hd.ThoiGianMua)=" + thang.ToString();
                str += " And Year(hd.ThoiGianMua)=" + nam.ToString();
                str += " Group By cthd.MaSanPham,cthd.GiaBan ";
            }
            else
            {
                str = "SELECT cthd.MaSanPham,Sum(cthd.SoLuongMua) as SoLuongMua,cthd.GiaBan";
                str += " FROM ChiTietHoaDon cthd, HoaDon hd ";
                str += " WHERE cthd.MaHoaDon = hd.MaHoaDon AND hd.MaTinhTrangHoaDon=1";
                str += " And Year(hd.ThoiGianMua)=" + nam.ToString();
                str += " Group By cthd.MaSanPham,cthd.GiaBan ";
            }
            cnn.Open();
            SqlCommand comand = new SqlCommand(str, cnn);
            SqlDataAdapter adapter = new SqlDataAdapter(comand);
            DataTable dt = new DataTable();
            adapter.Fill(dt);
            cnn.Close();
            return dt;
        }
        //ham lay 10 san pham ban nhieu nhat
        public DataTable SanPhamTop10SoLuongBan()
        {
            SqlConnection cnn = new SqlConnection(DataProvider.ConnectionString);
            string str;
            str = "SELECT TOP 5 cthd.MaSanPham,Sum(cthd.SoLuongMua) as SoLuongBan,cthd.GiaBan";
            str += " FROM ChiTietHoaDon cthd, HoaDon hd ";
            str += " WHERE cthd.MaHoaDon = hd.MaHoaDon AND hd.MaTinhTrangHoaDon=1 ";
            str += " Group By cthd.MaSanPham,cthd.GiaBan ";
            str += " ORDER BY SoLuongBan DESC";
            cnn.Open();
            SqlCommand comand = new SqlCommand(str, cnn);
            SqlDataAdapter adapter = new SqlDataAdapter(comand);
            DataTable dt = new DataTable();
            adapter.Fill(dt);
            cnn.Close();
            return dt;
        }
        //ham lay 10 san pham ban nhieu nhat theo loai
        public DataTable SanPhamTop10SoLuongBanTheoLoai(int malsp)
        {
            SqlConnection cnn = new SqlConnection(DataProvider.ConnectionString);
            string str;
            str = "SELECT TOP 2 cthd.MaSanPham,Sum(cthd.SoLuongMua) as SoLuongBan,cthd.GiaBan";
            str += " FROM ChiTietHoaDon cthd, HoaDon hd, SanPham sp ";
            str += " WHERE cthd.MaHoaDon = hd.MaHoaDon AND hd.MaTinhTrangHoaDon=1 And cthd.MaSanPham=sp.MaSanPham And sp.MaLoaiSanPham="+malsp .ToString ();
            str += " Group By cthd.MaSanPham,cthd.GiaBan ";
            str += " ORDER BY SoLuongBan DESC";
            cnn.Open();
            SqlCommand comand = new SqlCommand(str, cnn);
            SqlDataAdapter adapter = new SqlDataAdapter(comand);
            DataTable dt = new DataTable();
            adapter.Fill(dt);
            cnn.Close();
            return dt;
        }
        //ham lay hoa don cua khach hang
        public DataTable SanPhamCuaKhachHangDaMua(int mand)
        {
            SqlConnection cnn = new SqlConnection(DataProvider.ConnectionString);
            string str;
            str = "SELECT cthd.MaSanPham,Sum(cthd.SoLuongMua) as SoLuongBan,cthd.GiaBan, hd.ThoiGianMua";
            str += " FROM ChiTietHoaDon cthd, HoaDon hd";
            str += " WHERE cthd.MaHoaDon = hd.MaHoaDon AND hd.MaTinhTrangHoaDon=1 And cthd.MaNguoiDung="+mand.ToString();
            str += " Group By cthd.MaSanPham,cthd.GiaBan,hd.ThoiGianMua ";
            str += " ORDER BY hd.ThoiGianMua ASC";
            cnn.Open();
            SqlCommand comand = new SqlCommand(str, cnn);
            SqlDataAdapter adapter = new SqlDataAdapter(comand);
            DataTable dt = new DataTable();
            adapter.Fill(dt);
            cnn.Close();
            return dt;
        }
    }
}
